﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using NLBS.Entity;
using System.Data;

namespace NLBS.Data
{
    public partial class SqlDataProvider : DbProvider
    {
        #region Category

        public override void MoveCategory(string catIDList, int newCatID)
        {
            string[] catList = catIDList.Split(new char[] { ',' });
            DbCommand dbCommand = db.GetSqlStringCommand(string.Format(@"
            UPDATE blog_article SET log_catid=@newCatID where log_catid in ({0});", catIDList));
            db.AddInParameter(dbCommand, "catIDList", DbType.String, catIDList);
            db.AddInParameter(dbCommand, "newCatID", DbType.Int32, newCatID);
            db.ExecuteNonQuery(dbCommand);
            
            dbCommand = db.GetSqlStringCommand(string.Format(@"
            UPDATE blog_category SET cat_articleCount=0 where cat_id in ({0});", catIDList));
            db.ExecuteNonQuery(dbCommand);
            
            dbCommand = db.GetSqlStringCommand(string.Format(@"
            select count(log_id) from blog_article where log_catID={0};", newCatID));
            int count=Convert.ToInt32(db.ExecuteScalar(dbCommand).ToString());
            dbCommand = db.GetSqlStringCommand(string.Format(@"
            UPDATE blog_category SET cat_articleCount={0} where cat_id={1}", count, newCatID));
            db.ExecuteNonQuery(dbCommand);
        }

        public override IList<Category> GetCategory()
        {
            string strSQL=@"SELECT * FROM dbo.blog_Category
            ORDER BY cat_order";
            //DbCommand dbCommand = db.GetSqlStringCommand(strSQL);
            IList<Category> lists = new List<Category>();
            using (IDataReader dataReader =ExecuteReader(strSQL))
            {
                while (dataReader.Read())
                {
                    Category catrgory = CommonDataProvider.PopulateCategoryFromIDataReader(dataReader);
                    lists.Add(catrgory);
                }
            }
            return lists;
        }

        public override Category GetCategoryByID(int catID)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"SELECT cat_id, cat_name, cat_order, cat_articleCount, cat_hidden, cat_locked
            FROM      blog_Category
            WHERE   (cat_id = @catID)");
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            IDataReader dr = db.ExecuteReader(dbCommand);
            Category catrgory = null;
            if (dr.Read())
            {
                catrgory = CommonDataProvider.PopulateCategoryFromIDataReader(dr);
            }
            return catrgory;
        }

        public override int InsertCategory(Category cat)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"INSERT INTO [dbo].[blog_Category] 
            ([cat_name], [cat_order], [cat_articleCount], [cat_hidden], [cat_locked]) 
            VALUES 
            (@catname, @catorder, @catarticleCount, @cathidden, @catlocked)
            SELECT @catID=@@IDENTITY");
            db.AddInParameter(dbCommand, "catName", DbType.String, cat.Name);
            db.AddInParameter(dbCommand, "catOrder", DbType.Int32, cat.Order);
            db.AddInParameter(dbCommand, "catArticleCount", DbType.Int32, cat.ArticleCount);
            db.AddInParameter(dbCommand, "catHidden", DbType.Boolean, cat.Hidden);
            db.AddInParameter(dbCommand, "catLocked", DbType.Boolean, cat.Locked);
            db.AddOutParameter(dbCommand, "catID", DbType.Int32, 10);
            db.ExecuteNonQuery(dbCommand);
            int result = Convert.ToInt32(db.GetParameterValue(dbCommand, "@catID"));
            return result;
        }

        public override void UpdateCategory(Category cat)
        { 
            DbCommand dbCommand = db.GetSqlStringCommand(@"UPDATE [blog_Category] SET 
            [cat_name] = @catname, [cat_order] = @catorder, [cat_articleCount] = @catarticleCount,
            [cat_hidden] = @cathidden, [cat_locked] = @catlocked WHERE (([cat_id] = @catid))");
            db.AddInParameter(dbCommand, "catID", DbType.String, cat.CategoryID);
            db.AddInParameter(dbCommand, "catName", DbType.String, cat.Name);
            db.AddInParameter(dbCommand, "catOrder", DbType.Int32, cat.Order);
            db.AddInParameter(dbCommand, "catArticleCount", DbType.Int32, cat.ArticleCount);
            db.AddInParameter(dbCommand, "catHidden", DbType.Boolean, cat.Hidden);
            db.AddInParameter(dbCommand, "catLocked", DbType.Boolean, cat.Locked);
            db.ExecuteNonQuery(dbCommand);
        }

        public override void DeleteCategory(int catID)
        {
            DbCommand dbCommand = db.GetSqlStringCommand(@"DELETE FROM [blog_Category] 
            WHERE (([cat_id] = @catID))
            EXEC UpdateCategoryCount
            @catID=@catID");
            db.AddInParameter(dbCommand, "catID", DbType.Int32, catID);
            db.ExecuteNonQuery(dbCommand);
        }

        #endregion
    }
}
